package library.database.user;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class DatabaseBookProxy extends DatabaseProxy{
	protected static Connection conn=getConnection();
	protected PreparedStatement ps;
	public DatabaseBookProxy() throws IOException, ClassNotFoundException, SQLException {
		super();
		conn.setAutoCommit(false);
	}
	/**
	 * 新增书本前要调用：查询该书本id是否已存在
	 * @param id
	 * @return
	 * @throws SQLException
	 * @tips:不可被外部调用
	 */
	public boolean isExisted(int id) throws SQLException {
		ResultSet rs = showBookbyId(id);
		if(rs.next()==false) {
			return false;
		}
		return true;
	}
	/**
	 * 返回书籍列表
	 * @return
	 * @throws SQLException
	 */
	public ResultSet showBooks() throws SQLException {
		try{
			conn.setAutoCommit(false);
			String sql = "SELECT * FROM book;";
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			conn.commit();
			return rs;
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * 返回对应id名的书籍列表
	 * @param bkid
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet showBookbyId(int bkid) throws SQLException {
		try{
			conn = getConnection();
			String sql = "SELECT * FROM book where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, bkid);
			ResultSet rs = ps.executeQuery();
			conn.commit();
			return rs;
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * 返回对应类型的书籍列表
	 * @param type
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet showBookbytype(String type) throws SQLException {
		try{
			conn.setAutoCommit(false);
			String sql = "SELECT * FROM book where type like \'%?%\';"; //后面是查找包含该字段的书名
			ps = conn.prepareStatement(sql);
			ps.setString(1, type);
			ResultSet rs = ps.executeQuery();
			conn.commit();
			return rs;
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * 返回对应书名的书籍列表
	 * @param name
	 * @return
	 * @throws SQLException
	 */
	public ResultSet showBookbyName(String name) throws SQLException {
		try{
			conn.setAutoCommit(false);
			String sql = "SELECT * FROM book where name like \'%?%\';";
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			conn.commit();
			return rs;
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
			return null;
		}

	}
	/**
	 * 返回对应作者的书籍列表
	 * @param author
	 * @return
	 * @throws SQLException
	 */
	public ResultSet showBookbyAuthor(String author) throws SQLException {
		try{
			conn.setAutoCommit(false);
			String sql = "SELECT * FROM book where author like \'%?%\';";
			ps = conn.prepareStatement(sql);
			ps.setString(1,author);
			ResultSet rs = ps.executeQuery();
			conn.commit();
			return rs;
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
			return null;
		}

	}
	/**
	 * 管理员专用，根据id删除图书。
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean deleteBook(int id) throws SQLException {
		try{
			conn.setAutoCommit(false);
			conn = getConnection();
			String sql = "DELETE FROM book where id = ?;";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.execute();
			deletebookfromusrbk(id);
			conn.commit();
			return true;
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
			return false;
		}
	}
	/**
	 * 管理员专用，新增图书信息
	 * @param name
	 * @param id
	 * @param author
	 * @param type
	 * @param price
	 * @param borrowed
	 * @param num
	 * @return
	 * @throws SQLException
	 */
	public boolean addBook(String name,String author,String type,double price,int num) throws SQLException {
		try{
			conn.setAutoCommit(false);
			String sql = "INSERT INTO book values(?,?,?,?,?,?);";
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setInt(2, 0);//这里是啥都行
			ps.setString(3,author);
			ps.setString(4, type);
			ps.setDouble(5,price);
			ps.setInt(6, num);//
			try {
				ps.execute();
				conn.commit();
				return true;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				conn.rollback();
				e.printStackTrace();
				return false;
			}
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
			return false;
		}
	}
	/**
	 * 管理员专用，修改书本信息 
	 * @return 
	 * @throws SQLExint
	 * @tips 1-改书名，2-改书的作者，3-改书的类型，4-改书的价格，5-改书的库存数量
	 */
	public void modify(int func,String info,int id) throws SQLException { 
		boolean flag1 = isExisted(id);
		if(flag1 == false) {
			return;
		}
		String sql = null;
		switch(func) {
			case 1: 
				sql ="UPDATE book set name = ? Where id = ?;"; //修改用户姓名 
				break;
			case 2:
				sql = "UPDATE book set author= ? where id = ?;";//修改用户电子邮件
				break;
			case 3:
				sql ="UPDATE book set type=? where id = ?;";//修改用户部门
				break;
			case 4:
				sql ="UPDATE book set price=? where id = ?;";//修改用户权限
				break;
			case 5:
				sql="UPDATE book set num =? where id = ?;";//修改这本书的剩余数量，这里也有相关数量限制
				break;
	}
	try{
		conn.setAutoCommit(false);
		ps = conn.prepareStatement(sql);
		if(func==4) 
			{ps.setDouble(1, Double.parseDouble(info));}
		else if(func==5)
			{ps.setInt(1,Integer.valueOf(info));}
		else 
			ps.setString(1, info);
		ps.setInt(2, id);
		ps.execute(); 
		conn.commit();
	}catch(Exception e){
		conn.rollback();
		e.printStackTrace();
	}

}
	/**
	 * 读者专用，返回借阅书籍列表
	 * @param id
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet showMyborrow(int userid) throws SQLException {
		try{
			conn.setAutoCommit(false);
			String sql = "SELECT * FROM usrbk where userid = ?;";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ResultSet rs = ps.executeQuery();
			conn.commit();
			return rs;
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * 读者专用，借阅书籍 -->第三张表操作 修改图书记录的borrowed
	 * @param bkid
	 * @param userid
	 * @throws SQLException 
	 */
	public boolean borrowBooks(int bkid, int userid) throws SQLException {
		try{
			conn.setAutoCommit(false);
			String sql = "INSERT INTO usrbk values(?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.setInt(2, bkid);
			Date d = new Date();
			java.sql.Date date = new java.sql.Date(d.getTime());
			ps.setDate(3, date);
			ps.setDate(4,date);//这里的时间可以随便填，Mysql已经自动实现日期加一了。
			ps.execute();
			modify(5,String.valueOf(CheckNum(bkid)-1),bkid);
			conn.commit();
			return true;
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
			return false;
		}

	}
	/**
	 * 读者专用，归还书籍 
	 * @param bkid
	 * @param userid
	 * @throws SQLException 
	 */
	public boolean returnBooks(int bkid,int userid) throws SQLException {
		try{
			conn.setAutoCommit(false);
			String sql = "Delete FROM usrbk where userid = ? and bkid = ?;";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.setInt(2, bkid);
			ps.execute();
			conn.commit();
			modify(5,String.valueOf(CheckNum(bkid)+1),bkid);
			return true;
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
			return false;
		}		
	} 
	/**
	 * 返回该书的架上数量
	 * @param bkid
	 * @return
	 * @throws SQLException
	 */
	public int CheckNum(int bkid) throws SQLException{
		conn.setAutoCommit(false);
		boolean flag = isExisted(bkid);
		if(!flag) return -1;
		ResultSet rs = showBookbyId(bkid);
		int num = -1;
		while(rs.next())
		{
			num = rs.getInt("num");
		}
		return num;
	}
	/**
	 * 返回读者所查找的自己的借阅的其中一本书
	 * @param userid
	 * @param bkid
	 * @return
	 * @throws SQLException
	 */
	public ResultSet showSpecifiedBook(int userid,int bkid) throws SQLException{
		try {
			conn.setAutoCommit(false);
			String sql = "select * from usrbk where userid = ? and bkid = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,userid);
			ps.setInt(2, bkid);
			ResultSet rs = ps.executeQuery();
			conn.commit();
			return rs;
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * 从第三张表中删除关于该本书的所有借阅信息
	 * @param bkid
	 * @return
	 * @throws SQLException
	 */
	public boolean deletebookfromusrbk(int bkid) throws SQLException{
		try {
			conn.setAutoCommit(false);
			String sql = "Delete From usrbk where bkid = ?;";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,bkid);
			ps.execute();
			conn.commit();
			return true;
		}catch(Exception e)
		{
			conn.rollback();
			e.printStackTrace();
			return false;
		}
	}
	public ResultSet getBorrowedBooks(int bkid) throws SQLException{
		try {
			conn.setAutoCommit(false);
			String sql = "SELECT * From usrbk where bkid = ?;";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,bkid);
			ResultSet rs =ps.executeQuery();
			conn.commit();
			return rs;
		}catch(Exception e)
		{
			conn.rollback();
			e.printStackTrace();
			return null;
		}
	}

}
